package com.kamikaze;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

public class Member {
	String user_id;
	String user_pw;
	String user_name;
	String user_gender;
	String user_birthday;
	String user_employee;
	String user_tel;
	String user_mobile;
	String user_address;
	String user_salary;
	String dept_number;
	String user_level;
	String user_image;
	int login_count;
	int u_level;
	ArrayList<String> errors = new ArrayList<String>();
	ArrayList<Member> member = new ArrayList<Member>();
	boolean logon = false;
	boolean unlock = false;

	String url = "jdbc:mysql://j.snpy.org/j2?user=team2&password=kamikaze&useUnicode=true&characterEncoding=UTF-8";

	public Member() {
	}

	public Member(String user_id) {
		this.user_id = user_id;
	}

	public Member(String user_id, String user_pw) {
		super();
		this.user_id = user_id;
		this.user_pw = user_pw;
	}

	public Member(String user_id, String user_pw, String user_name,
			String user_gender, String user_birthday, String user_employee,
			String user_tel, String user_mobile, String user_address,
			String user_salary, String dept_number, String user_level,
			String user_image) {
		super();
		this.user_id = user_id;
		this.user_pw = user_pw;
		this.user_name = user_name;
		this.user_gender = user_gender;
		this.user_birthday = user_birthday;
		this.user_employee = user_employee;
		this.user_tel = user_tel;
		this.user_mobile = user_mobile;
		this.user_address = user_address;
		this.user_salary = user_salary;
		this.dept_number = dept_number;
		this.user_level = user_level;
		this.user_image = user_image;
	}

	public boolean login() {
		if (user_id != null && user_pw.length() > 0) {
			try {
				Class.forName("com.mysql.jdbc.Driver");
				Connection con = DriverManager.getConnection(url);
				PreparedStatement pstmt = con
						.prepareStatement("select * from users where user_id=? and user_pw=?");
				pstmt.setString(1, user_id);
				pstmt.setString(2, user_pw);
				ResultSet rs = pstmt.executeQuery();
				if (rs.next()) {
					logon = true;
					login_count = 0;
					login_count(login_count, user_id);
					u_level = rs.getInt("user_level");
					
				} else {
					login_count = check_count(user_id);
					login_count++;
					login_count(login_count, user_id);
				}
				rs.close();
				pstmt.close();
				con.close();
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return logon;
	}

	public int check_count(String user_id) {
		String syntax = "select * from users where user_id=?";
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(url);
			PreparedStatement pstmt = conn.prepareStatement(syntax);
			pstmt.setString(1, user_id);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				login_count = rs.getInt("login_count");
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return login_count;
	}

	public void login_count(int login_count, String user_id) {
		String syntax = "update users set login_count=? where user_id=?";
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(url);
			PreparedStatement pstmt = conn.prepareStatement(syntax);
			pstmt.setInt(1, login_count);
			pstmt.setString(2, user_id);
			pstmt.executeUpdate();
			pstmt.close();
			conn.close();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public boolean check() {
		boolean passed = true;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection con = DriverManager.getConnection(url);
			PreparedStatement pstmt = con
					.prepareStatement("select * from users where user_id");
			ResultSet rs = pstmt.executeQuery();
			rs.close();
			pstmt.close();
			con.close();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		// 姓名驗證
		if (user_name == null && user_name.length() < 2) {
			errors.add("請輸入姓名");
			passed = false;
		}
		// 密碼驗證
		if (!user_pw.matches("[a-zA-Z0-9]{8,12}")) {
			errors.add("密碼需為英數混合之八碼");
			passed = false;
		}
		// 生日驗證
		if (!user_birthday
				.matches("([20]|[19]){2}[0-3]{1}[0-9]{1}-[0-1]{1}[0-2]{1}-[0-3]{1}[0-9]{1}")) {
			errors.add("生日格式需為西元 年、月、日");
			passed = false;
		}
		// 任用日期
		try {
			Date date = new Date();
			DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
			Date today = df.parse(df.format(date));
			if (!user_employee
					.matches("([20]|[19]){2}[0-3]{1}[0-9]{1}-[0-1]{1}[0-2]{1}-[0-3]{1}[0-9]{1}")) {
				errors.add("任用日期格式需為西元 年、月、日");
				passed = false;
			}
			java.sql.Date u_emp = java.sql.Date.valueOf(user_employee);
			if (u_emp.before(today)) {
				errors.add("任用日期不能在今天之前");
				passed = false;
			}
		} catch (ParseException e) {
			e.printStackTrace();
		}
		// 電話驗證
		if (!user_tel.matches("[0-9]{4}-[0-9]{4}")) {
			errors.add("請輸入格式為xxxx-xxxx之電話號碼");
			passed = false;
		}
		// 手機驗證
		if (!user_mobile.matches("[0]{1}[9]{1}[0-9]{2}-[0-9]{3}-[0-9]{3}")) {
			errors.add("請輸入格式為09xx-xxx-xxx之電話號碼");
			passed = false;
		}
		// 地址驗證
		if (!user_address.matches("[縣市][路街道][0-9]{1,4}[號]")) {
			errors.add("請輸格式為xx市(縣)xx路(街、道)xx號");
			passed = false;
		}
	/*	// 相片路徑驗證
		if (user_image.matches("")) {
			errors.add("請輸入路徑");
			passed = false;
		}
	*/	
		// 寫入資料庫
		if (passed) {
			int u_salary = Integer.parseInt(user_salary);
			int d_number = Integer.parseInt(dept_number);
			int u_lv = Integer.parseInt(user_level);
			try {
				Class.forName("com.mysql.jdbc.Driver");
				Connection con = DriverManager.getConnection(url);
				PreparedStatement pstmt = con
						.prepareStatement("insert into users(user_id, user_pw, user_name, "
								+ "user_gender, user_birthday, user_employee, user_tel, user_mobile, "
								+ "user_address, user_salary, dept_number, user_level, user_image)"
								+ " values(?,?,?,?,?,?,?,?,?,?,?,?,?)");
				pstmt.setString(1, user_id);
				pstmt.setString(2, user_pw);
				pstmt.setString(3, user_name);
				pstmt.setString(4, user_gender);
				pstmt.setString(5, user_birthday);
				pstmt.setString(6, user_employee);
				pstmt.setString(7, user_tel);
				pstmt.setString(8, user_mobile);
				pstmt.setString(9, user_address);
				pstmt.setInt(10, u_salary);
				pstmt.setInt(11, d_number);
				pstmt.setInt(12, u_lv);
				pstmt.setString(13, user_image);
				int row = pstmt.executeUpdate();
				System.out.println(row);
				if (row == 1) {
					System.out.println("新增成功");
				} else {
					System.out.println("失敗");
				}
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return passed;
	}

	public boolean isLogon() {
		return logon;
	}

	public void setLogon(boolean logon) {
		this.logon = logon;
	}

	public String getUser_id() {
		return user_id;
	}

	public void setUser_id(String user_id) {
		this.user_id = user_id;
	}

	public String getUser_pw() {
		return user_pw;
	}

	public void setUser_pw(String user_pw) {
		this.user_pw = user_pw;
	}

	public String getUser_name() {
		return user_name;
	}

	public void setUser_name(String user_name) {
		this.user_name = user_name;
	}

	public String getUser_gender() {
		Integer.parseInt(user_gender);
		return user_gender;
	}

	public void setUser_gender(String user_gender) {
		this.user_gender = user_gender;
	}

	public String getUser_birthday() {
		return user_birthday;
	}

	public void setUser_birthday(String user_birthday) {
		this.user_birthday = user_birthday;
	}

	public String getUser_employee() {
		return user_employee;
	}

	public void setUser_employee(String user_employee) {
		this.user_employee = user_employee;
	}

	public String getUser_tel() {
		return user_tel;
	}

	public void setUser_tel(String user_tel) {
		this.user_tel = user_tel;
	}

	public String getUser_mobile() {
		return user_mobile;
	}

	public void setUser_mobile(String user_mobile) {
		this.user_mobile = user_mobile;
	}

	public String getUser_address() {
		return user_address;
	}

	public void setUser_address(String user_address) {
		this.user_address = user_address;
	}

	public String getUser_salary() {

		return user_salary;
	}

	public void setUser_salary(String user_salary) {
		this.user_salary = user_salary;
	}

	public String getDept_number() {
		Integer.parseInt(user_gender);
		return dept_number;
	}

	public void setDept_number(String dept_number) {
		this.dept_number = dept_number;
	}

	public String getUser_level() {
		Integer.parseInt(user_gender);
		return user_level;
	}

	public void setUser_level(String user_level) {
		this.user_level = user_level;
	}

	public String getUser_image() {
		return user_image;
	}

	public void setUser_image(String user_image) {
		this.user_image = user_image;
	}

	public ArrayList<String> getErrors() {
		return errors;
	}

	public void setErrors(ArrayList<String> errors) {
		this.errors = errors;
	}

	public int getLogin_count() {
		return login_count;
	}

	public void setLogin_count(int login_count) {
		this.login_count = login_count;
	}

	public void setMember(ArrayList<Member> member) {
		this.member = member;
	}
	
	public int getU_level() {
		return u_level;
	}
	
	public void setU_level(int u_level) {
		this.u_level = u_level;
	}
	
	public boolean isUnlock() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(url);
			PreparedStatement pstmt = conn
					.prepareStatement("update users set login_count = 0 where user_id =?");
			pstmt.setString(1, user_id);
			int row = pstmt.executeUpdate();
			if(row==1){
				System.out.println("新增成功");
			}else{
				System.out.println("失敗");
			}
			pstmt.close();
			conn.close();
			unlock = true;
			errors.add("解鎖成功");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return unlock;
	}

	public void setUnlock(boolean unlock) {
		this.unlock = unlock;
	}

	public ArrayList<Member> getMember() {
		// 將各欄位值從資料庫取出並將值傳給servlet
		java.sql.Date u_emp = java.sql.Date.valueOf(user_employee);
		java.sql.Date u_bir = java.sql.Date.valueOf(user_birthday);
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection con = DriverManager.getConnection(url);
			PreparedStatement pstmt = con
					.prepareStatement("insert into users(user_id, user_pw, user_name, "
							+ "user_gender, user_birthday, user_employee, user_tel, user_mobile, "
							+ "user_address, user_salary, dept_number, user_level, user_image)"
							+ " values(?,?,?,?,?,?,?,?,?,?,?,?,?)");
			pstmt.setString(1, user_id);
			pstmt.setString(2, user_pw);
			pstmt.setString(3, user_name);
			pstmt.setString(4, user_gender);
			pstmt.setDate(5, u_bir);
			pstmt.setDate(6, u_emp);
			pstmt.setString(7, user_tel);
			pstmt.setString(8, user_mobile);
			pstmt.setString(9, user_address);
			pstmt.setString(10, user_salary);
			pstmt.setString(11, dept_number);
			pstmt.setString(12, user_level);
			pstmt.setString(13, user_image);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				user_id = rs.getString("user_id");
				user_pw = rs.getString("user_pw");
				user_name = rs.getString(user_name);
				user_gender = rs.getString(user_gender);
				user_birthday = rs.getDate(user_birthday).toString();
				user_employee = rs.getDate(user_employee).toString();
				user_tel = rs.getString(user_tel);
				user_mobile = rs.getString(user_mobile);
				user_address = rs.getString(user_address);
				user_salary = rs.getString(user_salary);
				dept_number = rs.getString(dept_number);
				user_level = rs.getString(user_level);
				Member m = new Member(user_id, user_pw, user_name, user_gender,
						user_birthday, user_employee, user_tel, user_mobile,
						user_address, user_salary, dept_number, user_level,
						user_image);
				member.add(m);
			}
			rs.close();
			pstmt.close();
			con.close();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return member;
	}
	public boolean csalary(String user_id){
		boolean cs = false;
		java.util.Date date = new Date();
		DateFormat   f   =   new   SimpleDateFormat( "yyyyMM"); 
		String nowdate =f.format(date);
		int now = Integer.valueOf(nowdate);
		String csal = "select * from month_salary where user_id=? and month=?";
		
		String isal = "INSERT INTO `j2`.`month_salary` "
				+ "( `user_id`,`month`,`msalary`) "
				+ "VALUES (?,?,?);";
		try {
			String fsal = "select * from users where user_id = ?";
			Class.forName("com.mysql.jdbc.Driver");
		Connection connect = DriverManager.getConnection(url);
		PreparedStatement pstatem = connect.prepareStatement(fsal);
		pstatem.setString(1, user_id);
		ResultSet resu = pstatem.executeQuery();	
		resu.next();
		int sal = resu.getInt("user_salary");
		System.out.println(sal);
		resu.close();
		pstatem.close();
		connect.close();
			
			//查看有無當月的薪資資料
			Class.forName("com.mysql.jdbc.Driver");
		Connection connec = DriverManager.getConnection(url);
		PreparedStatement pstate = connec.prepareStatement(csal);
		pstate.setString(1, user_id);
		pstate.setInt(2, now);
		ResultSet res = pstate.executeQuery();
	
		if(!res.next()){
			//沒有的話就建立新月份資料
			Class.forName("com.mysql.jdbc.Driver");
			Connection connect1 = DriverManager.getConnection(url);
			PreparedStatement pstatem1 = connect1.prepareStatement(isal);
			pstatem1.setString(1, user_id);
			pstatem1.setInt(2, now);
			pstatem1.setInt(3, sal);
			int row1=pstatem1.executeUpdate();
			System.out.println("薪水增加"+row1+"筆");
			pstatem1.close();
			connect1.close();
		}
		res.close();
		pstate.close();
		connec.close();

		} catch (ClassNotFoundException e) {e.printStackTrace();} 
		catch (SQLException e) {e.printStackTrace();}
		
		return cs;
	}
	//檢查shift裡的attendance status
	public boolean check_attendance_status(String user_id){
		boolean done=false;
		Member member = new Member();
		java.util.Date date = new Date();
		DateFormat   f   =   new   SimpleDateFormat( "yyyyMM"); 
		DateFormat   i   =   new   SimpleDateFormat( "yyyyMMdd"); 
		String nowdate =f.format(date);
		String nowdate01 =i.format(date);
		int nowym = Integer.valueOf(nowdate);
		int nowymd = Integer.valueOf(nowdate01);
		String attendance ="SELECT s.user_id,s.shift_number,s.shift_date,wt.wt_number,wt.on_time,wt.off_time,cd.clock_number,cd.clock_in,cd.clock_out,s.attendance_status , l.leaved_number " +
				"FROM `shift`s left outer join clockdata cd on s.shift_number=cd.shift_number left outer join leavedata l on l.shift_number = s.shift_number,worktypes wt " +
				"where s.wt_number = wt.wt_number and s.user_id = ? and DATE_FORMAT( s.shift_date,'%Y%m' )= ? and s.shift_date<=?";
		
		
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection con01= DriverManager.getConnection(url);
			PreparedStatement pstat01 = con01.prepareStatement(attendance);
			pstat01.setString(1, user_id);
			pstat01.setInt(2, nowym);
			pstat01.setInt(3, nowymd);
			ResultSet rs01 = pstat01.executeQuery();
			while(rs01.next()){
				int status = 0;
				Date shift_date = rs01.getDate("shift_date");
				int shift_number = rs01.getInt("shift_number");
				int leaved_number = rs01.getInt("leaved_number");
				System.out.print("leaved_number="+leaved_number);
				int attendance_status = rs01.getInt("attendance_status");
				System.out.print("attendance_status ="+attendance_status);
			//	if(attendance_status==0){
					int wt_number = rs01.getInt("wt_number");
					if( wt_number == 3){status = 3;member.set_att_status(status, user_id, shift_number);}//正常休假
					else{
						String clock_number=rs01.getString("clock_number");
						if(clock_number==null){status = 4; member.set_att_status(status, user_id, shift_number);}//曠職
						else{
								Time clock_in=rs01.getTime("clock_in");
								System.out.println("clock_in"+ clock_in);
								Time clock_out=rs01.getTime("clock_out");
								System.out.println("clock_out"+clock_out);
								Time on_time=rs01.getTime("on_time");
								System.out.println("on_time"+on_time);
								Time off_time=rs01.getTime("off_time");
								System.out.println("off_time"+off_time);
								if (clock_in==null){status = 7;member.set_att_status(status, user_id, shift_number);}//未打上班卡
								else{
									if (clock_out==null){status = 8;member.set_att_status(status, user_id, shift_number);}//未打下班卡
									else{
										if (on_time.before(clock_in) && off_time.after(clock_out)){status = 9;member.set_att_status(status, user_id, shift_number);}//遲到加早退
										else{
											if (off_time.after(clock_out)){status = 2;member.set_att_status(status, user_id, shift_number);}//早退
											else{
												if (on_time.before(clock_in)){status = 1;member.set_att_status(status, user_id, shift_number);}//遲到
												else{status = 5;member.set_att_status(status, user_id, shift_number);}//正常出勤
											}
										}
									}
								}
							}
					}
					member.set_att_status(status, user_id, shift_number);

			//	}
			//	else{System.out.print("attendance 檢查完畢");}
				System.out.println(shift_date+"and" + status);
			}
	
		} catch (ClassNotFoundException e) {e.printStackTrace();} 
		catch (SQLException e) {e.printStackTrace();}

		return done;
	}
	//寫入attendance status
	public void set_att_status(int status,String user_id,int shift_number){
		String insert_att_status = "UPDATE  `j2`.`shift` SET  `attendance_status` = ? where user_id = ? and shift_number= ?";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		Connection con02= DriverManager.getConnection(url);
		PreparedStatement pstat02 = con02.prepareStatement(insert_att_status);
		pstat02.setInt(1, status);
		pstat02.setString(2, user_id);
		pstat02.setInt(3, shift_number);
		int row02 = pstat02.executeUpdate();
		System.out.println(row02);
		} catch (ClassNotFoundException e) {e.printStackTrace();} 
		catch (SQLException e) {e.printStackTrace();}
	}
	
	
}